{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["SII CI calculation details","SII CI functions"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"SII CI calculation details","index":0,"n_rows":33,"n_cols":17,"rows_kept":33,"cut":false,"hidden":false,"merged":[[32,7,32,11],[3,13,3,15],[29,7,29,11],[3,8,3,11],[3,0,3,4],[3,5,3,7]]}
["This sheet demonstrates the methodology step by step.  The next sheet condenses all this into two functions, held in a single row, to enable multiple areas."]
[]
[]
["values from decile LE calculations",null,null,null,null,"SE derived from LE CI",null,null,"parameters for SII calculation",null,null,null,null,"val simulations (x eg 10,000)"]
["decile","val","LCL","UCL","population",null,"SE",null,"a","b","sqrt(a)","bsqrt(a)",null,1,2,3,"…"]
[1,75.933459010308098,72.722053054645301,79.144864965970797,7291,null,{"$f":"(D6-C6)/1.96/2","v":1.6384724263585451},null,{"$f":"E6/SUM($E$6:$E$15)","v":0.1066621803499327},{"$f":"I6/2","v":5.3331090174966352E-2},{"$f":"SQRT(I6)","v":0.32659176405710649},{"$f":"J6*K6","v":1.741749481933088E-2},null,{"$f":"_xlfn.NORM.INV(genrand_real2b(),$B6,$G6)","v":79.949711027343767},{"$f":"_xlfn.NORM.INV(genrand_real2b(),$B6,$G6)","v":76.12785103247144},75.914916265680432]
[2,78.344238272784295,75.250980219080205,81.4374963264884,7997,null,1.578192884542907,null,0.1169904617005091,{"$f":"I7/2+I6/2+J6","v":0.16515741120018726},0.34203868450879804,5.6490223663790681E-2,null,{"$f":"_xlfn.NORM.INV(genrand_real2b(),$B7,$G7)","v":79.747825932282751},{"$f":"_xlfn.NORM.INV(genrand_real2b(),$B7,$G7)","v":78.000821196978649},80.680768795121267]
[3,83.836063104165405,80.869729512475899,86.802396695854895,6105,null,1.5134355059640296,null,8.9311838024460175E-2,0.26830856106267187,0.29885086251249166,8.0184244893065026E-2,null,86.657724077016823,84.700905981388289,83.44406649029203]
[4,83.637539966493094,80.157578356035998,87.117501576950104,7666,null,1.7754906175801293,null,0.11214816548657031,0.36903856281818714,0.33488530198647165,0.12358559055402209,null,85.367131456655699,82.531743872125247,85.972805605691264]
[5,80.465503152955904,77.130878596927502,83.800127708984206,5790,null,1.7013390591981388,null,8.4703610509684599E-2,0.4674644508163146,0.29103884708004979,0.13605031481648885,null,78.524699193870433,81.345254347383062,82.782455258287868]
[6,81.115675985702197,78.049998681117799,84.181353290286594,6934,null,1.5641210737675499,null,0.1014395224998537,0.56053601732108371,0.31849571818134964,0.178528321403192,null,79.393784778164502,83.656757380221109,77.306052105466222]
[7,81.693083571856207,79.011578491948001,84.374588651764398,5918,null,1.3681148366878566,null,8.6576160102990227E-2,0.65454385862250564,0.29423827096927796,0.19259185323464559,null,80.483841724318268,83.103495788599602,82.155654691957707]
[8,84.190006206098701,81.445728276934304,86.934284135263098,5974,null,1.4001418005940802,null,8.7395400550061439E-2,0.74152963894903146,0.29562713094379789,0.21921627967229249,null,84.354923769434279,81.545416259952333,82.375850363942618]
[9,80.595564108147201,75.827185168894005,85.363943047400397,7147,null,2.4328463975781616,null,0.10455556205746387,0.83750512025279411,0.3233505250613703,0.27080772037532702,null,79.16673238988372,80.712587471013691,82.774028946380284]
[10,86.289434957862397,83.2190593454592,89.359810570265594,7534,null,1.5665181695934678,null,0.11021709871847388,0.94489145064076296,0.33198960634103275,0.31369414073323426,null,85.50452694757162,86.967564069019687,85.085243631201251]
["max",85.011247255985495,0,0,null,null,null,null,null,0.99999999999999989]
["SII",6.9346164736688296,0.67756406620724996,13.191668881130401,68356,null,null,null,null,null,null,null,null,"val x sqrt(a) simulations (x eg 10,000)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"N6*$K6","v":26.110917160276102},{"$f":"O6*$K6","v":24.862729162571462},24.793186421456099]
[null,null,null,null,null,null,null,{"$f":"LINEST(N18:N27,$K$6:$L$15,FALSE,FALSE)","v":1.6361709753178137},81.099178346366799,0,null,null,null,{"$f":"N7*$K7","v":27.276841474314605},26.679298272820546,27.59594402384176]
[null,null,null,null,null,null,null,1.6361709753178137,81.099178346366799,0,null,null,null,{"$f":"N8*$K8","v":25.897735583785991},25.312938808127353,24.937331242173475]
[null,null,null,null,null,null,null,1.6361709753178137,81.099178346366799,0,null,null,null,{"$f":"N9*$K9","v":28.588197597580969},27.638667970086793,28.791028967886142]
[null,null,null,null,null,null,null,1.6361709753178137,81.099178346366799,0,null,null,null,{"$f":"N10*$K10","v":22.853737920691767},23.674629040695773,24.092910336827906]
[null,null,null,null,null,null,null,1.6361709753178137,81.099178346366799,0,null,null,null,{"$f":"N11*$K11","v":25.286580502057006},26.644319022536443,24.621646585095302]
[null,null,null,null,null,null,null,1.6361709753178137,81.099178346366799,0,null,null,null,{"$f":"N12*$K12","v":23.68142642992844},24.452228912340221,24.173337786910682]
[null,null,null,null,null,null,null,1.6361709753178137,81.099178346366799,0,null,null,null,{"$f":"N13*$K13","v":24.937604094940635},24.107037450547434,24.352536302147964]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"N14*$K14","v":25.598604485661891},26.098457537814056,26.765025721257128]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"N15*$K15","v":28.386614241700528},28.87232735971239,28.247416538553367]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"SII simulations (x eg 10,000)"]
[null,null,null,null,null,null,null,"SII calculated for each repetition -->",null,null,null,null,null,{"$f":"LINEST(N18:N27,$K$6:$L$15,FALSE,FALSE)","v":1.6361709753178137},{"$f":"LINEST(O18:O27,$K$6:$L$15,FALSE,FALSE)","v":7.1303884894621703},4.9836615667590101]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"RII simulations (x eg 10,000)"]
[null,null,null,null,null,0,null,"RII calculated for each repetition -->",null,null,null,null,null,{"$f":"(INDEX(LINEST(N18:N27,$K$6:$L$15,FALSE,FALSE),1,2)+INDEX(LINEST(N18:N27,$K$6:$L$15,FALSE,FALSE),1,1))/INDEX(LINEST(N18:N27,$K$6:$L$15,FALSE,FALSE),1,2)","v":1.0201749389905022},{"$f":"(INDEX(LINEST(O18:O27,$K$6:$L$15,FALSE,FALSE),1,2)+INDEX(LINEST(O18:O27,$K$6:$L$15,FALSE,FALSE),1,1))/INDEX(LINEST(O18:O27,$K$6:$L$15,FALSE,FALSE),1,2)","v":1.091154891240731},1.0628165797209503]

{"sheet":"SII CI functions","index":1,"n_rows":12,"n_cols":48,"rows_kept":12,"cut":false,"hidden":false,"merged":[[2,35,2,36],[3,35,3,36],[9,45,10,45],[9,46,10,47],[9,2,9,11],[9,12,9,21],[9,22,9,31],[9,42,10,42],[9,43,10,44],[9,32,9,41]]}
["Enter red and blue data in rows 12 onwards.  Copy functions in AQ12:AV12 down to include all rows of data.  See previous sheet for step by step demonstration of method."]
[]
["4 functions defined:",null,"SII(values, populations)",null,null,null,null,null,null,null,null,null,null,"values (required) is range of 10 values for decile indicator values laid out as below",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Change reps and CI here:",null,null,null,"Reps:",10000,null,"Warning: changing the number of reps to a large number (eg >100,000) may lock your "]
[null,null,"SIISimCI(values, lowerCIs, upperCIs, populations, reps, conf)",null,null,null,null,null,null,null,null,null,null,"lowerCIs (required) is range of 10 values for decile indicator lower confidence limits laid out as below",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"CI:",0.95,null,"PC for a long time - check with smaller numbers first to estimate time"]
[null,null,"RII(values, populations)",null,null,null,null,null,null,null,null,null,null,"upperCIs (required) is range of 10 values for decile indicator upper confidence limits laid out as below"]
[null,null,"RIISimCI(values, lowerCIs, upperCIs, populations, reps, conf)",null,null,null,null,null,null,null,null,null,null,"populations (required) is range of 10 values for decile populations laid out as below"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"reps (optional: default = 10,000) is number of repetitions for simulation"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"conf (optional: default = 0.95, ie 95% confidence intervals) is confidence level for CI"]
[]
[null,null,"Decile indicator values",null,null,null,null,null,null,null,null,null,"Decile indicator lower confidence limits",null,null,null,null,null,null,null,null,null,"Decile indicator upper confidence limits",null,null,null,null,null,null,null,null,null,"Decile populations",null,null,null,null,null,null,null,null,null,"SII","95% CI",null,"RII","95% CI"]
["Area","Decile",1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10]
["Area code 1",1,75.933459010308098,78.344238272784295,83.836063104165405,83.637539966493094,80.465503152955904,81.115675985702197,81.693083571856207,84.190006206098701,80.595564108147201,86.289434957862397,72.722053054645301,75.250980219080205,80.869729512475899,80.157578356035998,77.130878596927502,78.049998681117799,79.011578491948001,81.445728276934304,75.827185168894005,83.2190593454592,79.144864965970797,81.4374963264884,86.802396695854895,87.117501576950104,83.800127708984206,84.181353290286594,84.374588651764398,86.934284135263098,85.363943047400397,89.359810570265594,7291,7997,6105,7666,5790,6934,5918,5974,7147,7534,{"$f":"sii(C12:L12,AG12:AP12)","v":6.9346164736688189},{"$f":"siisimci(C12:L12,M12:V12,W12:AF12,AG12:AP12,$AJ$3,$AJ$4)","v":3.1787516760465939},10.754617532427218,{"$f":"rii(C12:L12,AG12:AP12)","v":1.0888180804446217},{"$f":"riisimci(C12:L12,M12:V12,W12:AF12,AG12:AP12,$AJ$3,$AJ$4)","v":1.0390496216163396},1.1423905504816094]